﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using DBUtility;
using IDAL;
using Model;

namespace DAL
{
    public class PackageProduct : IPackageProduct
    {
        #region  字段定义
        private const string PARM_TEST_PACKAGEPRODUCTSN = "@PackageProductSN";
        private const string PARM_TEST_PRODUCTSN = "@ProductSN";
        private const string PARM_TEST_SN3BK = "@SN3BK";
        private const string PARM_TEST_USERID = "@UserID";
        private const string PARM_TEST_UNIONTIME = "@UnionTime";
        private const string PARM_TEST_PACKER = "@Packer";
        private const string PARM_TEST_PACKAGETIME = "@PackageTime";
        private const string PARM_TEST_STARTTIME = "@starttime";
        private const string PARM_TEST_STOPTIME = "@stoptime";
        private const string PARM_TEST_PRODUCTTYPEID = "@ProductTypeID";
        private const string PARM_TEST_QUANTITY = "@Quantity";

        private const string PARM_TEST_AMOUNT = "@Amount";

        private const string PARM_TEST_STEPITEM = "@StepItem";
        private const string PARM_TEST_RESULT = "@result";
        private const string PARM_TEST_RESULTMESSAGE = "@ResultMessage";

        private const string PARM_PACKAGEPRODUCT_DT = "@ProductSNItemdt";
        #endregion


        #region    与电性能、交调相关
        /// <summary>
        /// 包装区获取腔体最后一次测试数据
        /// </summary>
        /// <param name="productSN"></param>
        /// <returns></returns>
        public TestInfo GetlastTest(string productSN)
        {
            TestInfo test = null;
            SqlParameter parm = new SqlParameter(PARM_TEST_PRODUCTSN, SqlDbType.NVarChar, 50);
            //Bind the parameter
            parm.Value = productSN;

            //Execute the query	
            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "Test_SelectforPackage", parm))
            {
                if (rdr.Read())

                    test = new TestInfo(rdr.GetGuid(0), rdr.GetValue(1).ToString(), rdr.GetDateTime(2), rdr.GetValue(3).ToString(), rdr.GetValue(4).ToString()
                        , rdr.GetValue(5).ToString(), rdr.GetValue(6).ToString(), rdr.GetValue(7).ToString(), rdr.GetValue(8).ToString());
                else
                    test = new TestInfo();
            }
            return test;
        }

        /// <summary>
        /// 关联前判断交调是否通过
        /// </summary>
        /// <param name="productSN"></param>
        /// <returns></returns>
        public PIMTestInfo GetlastPIMTest(string productSN)
        {
            PIMTestInfo test = null;
            SqlParameter parm = new SqlParameter(PARM_TEST_PRODUCTSN, SqlDbType.NVarChar, 50);
            //Bind the parameter
            parm.Value = productSN;

            //Execute the query	
            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PIMTest_SelectforUnion", parm))
            {
                if (rdr.Read())

                    test = new PIMTestInfo()
                    {
                        ProductSN = rdr.GetValue(0).ToString(),
                        TestTime = rdr.GetDateTime(1),
                        ProductTypeID = rdr.GetValue(2).ToString(),
                        UserID = rdr.GetValue(3).ToString(),
                        PCIP = rdr.GetValue(4).ToString(),
                        SignalSource1 = rdr.GetValue(5).ToString(),
                        SignalSource2 = rdr.GetValue(6).ToString(),
                        SpectrumAnalyzer = rdr.GetValue(7).ToString(),
                        TestStep = rdr.GetValue(8).ToString(),
                        IsPass = rdr.GetValue(9).ToString()
                    };
                else
                    test = new PIMTestInfo();
            }
            return test;
        }
        #endregion


        #region  操作相关
        public void Insert(PackageProductInfo ppi)
        {
            SqlParameter[] testParms = GetAllParameters();
            //Bind the parameter
            testParms[0].Value = ppi.PackageProductSN;
            testParms[1].Value = ppi.ProductSN;
            testParms[2].Value = ppi.ProductTypeID;
            testParms[3].Value = ppi.UserID;
            testParms[4].Value = ppi.UnionTime;
            SqlCommand cmd = new SqlCommand();
            foreach (SqlParameter parm in testParms)
                cmd.Parameters.Add(parm);
            using (SqlConnection conn = new SqlConnection(sqlHelper.ConnectionStringLocalTransaction))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "PackageTest_Insert";
                using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { }
            }
            cmd.Parameters.Clear();
        }

        public void Update(PackageProductInfo ppi)
        {
            SqlParameter[] testParms = GetAllParameters();
            //Bind the parameter
            testParms[0].Value = ppi.PackageProductSN;
            testParms[1].Value = ppi.ProductSN;
            testParms[2].Value = ppi.UserID;
            testParms[3].Value = ppi.UnionTime;
            SqlCommand cmd = new SqlCommand();
            foreach (SqlParameter parm in testParms)
                cmd.Parameters.Add(parm);
            using (SqlConnection conn = new SqlConnection(sqlHelper.ConnectionStringLocalTransaction))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "PackageTest_Update";
                using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { }
            }
            cmd.Parameters.Clear();
        }

        /// <summary>
        /// 包装过的成品做包装记录
        /// </summary>
        /// <param name="packageProductSN"></param>
        /// <param name="packer"></param>
        /// <param name="packageTime"></param>
        public void SetPackaged(string packageProductSN, string packer, DateTime packageTime)
        {
            SqlParameter[] testParms = GetPackageParameters();
            //Bind the parameter
            testParms[0].Value = packageProductSN;
            testParms[1].Value = packer;
            testParms[2].Value = packageTime;
            SqlCommand cmd = new SqlCommand();
            foreach (SqlParameter parm in testParms)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(sqlHelper.ConnectionStringLocalTransaction))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "PackageTest_setPackaged";
                using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { }
            }
            cmd.Parameters.Clear();
        }
        #endregion


        #region        参数部分

        private static SqlParameter[] GetPackageParameters()
        {
            SqlParameter[] parms = null;
            parms = new SqlParameter[]
                {
                    new SqlParameter(PARM_TEST_PACKAGEPRODUCTSN,SqlDbType.NVarChar,100),
                    new SqlParameter(PARM_TEST_PACKER, SqlDbType.NVarChar, 50),
                    new SqlParameter(PARM_TEST_PACKAGETIME,SqlDbType.DateTime,8)
                };
            return parms;
        }

        private static SqlParameter[] GetAllParameters()
        {
            SqlParameter[] parms = null;
            parms = new SqlParameter[]
                {
                    new SqlParameter(PARM_TEST_PACKAGEPRODUCTSN,SqlDbType.NVarChar,100),
					new SqlParameter(PARM_TEST_PRODUCTSN, SqlDbType.NVarChar, 50),
                    new SqlParameter(PARM_TEST_PRODUCTTYPEID,SqlDbType.NVarChar,50),
                    new SqlParameter(PARM_TEST_USERID, SqlDbType.NVarChar, 50),
                    new SqlParameter(PARM_TEST_UNIONTIME,SqlDbType.DateTime,8)
                };
            return parms;
        }

        private static SqlParameter[] GetPIDDTParameters()
        {
            SqlParameter[] parms = null;
            parms = new SqlParameter[]
                {
                    new SqlParameter(PARM_TEST_PRODUCTTYPEID,SqlDbType.NVarChar,50),
					new SqlParameter(PARM_TEST_STARTTIME, SqlDbType.DateTime, 8),
                    new SqlParameter(PARM_TEST_STOPTIME,SqlDbType.DateTime,8)
                };
            return parms;
        }

        private static SqlParameter[] GetUIDDTParameters()
        {
            SqlParameter[] parms = null;
            parms = new SqlParameter[]
                {
                    new SqlParameter(PARM_TEST_USERID,SqlDbType.NVarChar,20),
					new SqlParameter(PARM_TEST_STARTTIME, SqlDbType.DateTime, 8),
                    new SqlParameter(PARM_TEST_STOPTIME,SqlDbType.DateTime,8)
                };
            return parms;
        }

        private static SqlParameter[] GetDTParameters()
        {
            SqlParameter[] parms = null;
            parms = new SqlParameter[]
                {
					new SqlParameter(PARM_TEST_STARTTIME, SqlDbType.DateTime, 8),
                    new SqlParameter(PARM_TEST_STOPTIME,SqlDbType.DateTime,8)
                };
            return parms;
        }

        private static SqlParameter[] Get3BKParameters()
        {
            SqlParameter[] parms = null;
            parms = new SqlParameter[]
                {
                    new SqlParameter(PARM_TEST_PRODUCTTYPEID,SqlDbType.NVarChar,50),
					new SqlParameter(PARM_TEST_SN3BK,SqlDbType.NVarChar,50)
                };
            return parms;
        }

        private static SqlParameter[] GetPPSNPIDParameters()
        {
            SqlParameter[] parms = null;
            parms = new SqlParameter[]
                {
                    new SqlParameter(PARM_TEST_PACKAGEPRODUCTSN,SqlDbType.NVarChar,100),
					new SqlParameter(PARM_TEST_PRODUCTTYPEID,SqlDbType.NVarChar,50)
                };
            return parms;
        }

        private static SqlParameter[] GetPrinterParameters()
        {
            SqlParameter[] parms = null;
            parms = new SqlParameter[]
                {
                    new SqlParameter(PARM_TEST_PRODUCTTYPEID,SqlDbType.NVarChar,50),
					new SqlParameter(PARM_TEST_QUANTITY,SqlDbType.Int,4)
                };
            return parms;
        }

        private static SqlParameter[] GetPrintParameters()
        {
            SqlParameter[] parms = null;
            parms = new SqlParameter[]
                {
                    new SqlParameter(PARM_TEST_SN3BK,SqlDbType.NVarChar,50),
					new SqlParameter(PARM_TEST_AMOUNT,SqlDbType.Int,4)
                };
            return parms;
        }

        private static SqlParameter[] GetStepParameters()
        {
            SqlParameter[] parms = null;
            parms = new SqlParameter[]
                {
                    new SqlParameter(PARM_TEST_PRODUCTSN,SqlDbType.NVarChar,50),
					new SqlParameter(PARM_TEST_PRODUCTTYPEID,SqlDbType.NVarChar,50),
                    new SqlParameter(PARM_TEST_STEPITEM,SqlDbType.NVarChar,50),
					new SqlParameter(PARM_TEST_RESULT,SqlDbType.Bit,1)
                };
            return parms;
        }

        private static SqlParameter[] GetSNPIDParameters()
        {
            SqlParameter[] parms = null;
            parms = new SqlParameter[]
                {
                    new SqlParameter(PARM_TEST_PRODUCTSN,SqlDbType.NVarChar,50),
                    new SqlParameter(PARM_TEST_PRODUCTTYPEID,SqlDbType.NVarChar,50)
                };
            return parms;
        }

        private static SqlParameter[] GetSNRMParameters()
        {
            SqlParameter[] parms = null;
            parms = new SqlParameter[]
                {
                    new SqlParameter(PARM_TEST_PRODUCTSN,SqlDbType.NVarChar,50),
                    new SqlParameter(PARM_TEST_RESULTMESSAGE,SqlDbType.NVarChar,300)
                };
            return parms;
        }
        #endregion


        #region   检索、统计相关
        public PackageProductInfo GetPackageProductbyPackageProductSN(string packageProductSN)
        {
            PackageProductInfo test = null;
            SqlParameter parm = new SqlParameter(PARM_TEST_PACKAGEPRODUCTSN, SqlDbType.NVarChar, 100);
            //Bind the parameter
            parm.Value = packageProductSN;

            //Execute the query	
            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PackageTest_SelectbyPackageProductSN", parm))
            {
                if (rdr.Read())

                    test = new PackageProductInfo(rdr.GetValue(0).ToString(), rdr.GetValue(1).ToString(), rdr.GetValue(2).ToString(), rdr.GetValue(3).ToString(), rdr.GetDateTime(4), rdr.GetValue(5).ToString());
                else
                    test = new PackageProductInfo();
            }
            return test;
        }

        public PackageProductInfo GetPackageProductbyProductSN(string productSN)
        {
            PackageProductInfo test = null;
            SqlParameter parm = new SqlParameter(PARM_TEST_PRODUCTSN, SqlDbType.NVarChar, 50);
            //Bind the parameter
            parm.Value = productSN;

            //Execute the query	
            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PackageTest_SelectbyProductSN", parm))
            {
                if (rdr.Read())

                    test = new PackageProductInfo(rdr.GetValue(0).ToString(), rdr.GetValue(1).ToString(), rdr.GetValue(2).ToString(), rdr.GetValue(3).ToString(), rdr.GetDateTime(4));
                else
                    test = new PackageProductInfo();
            }
            return test;
        }

        /// <summary>
        /// 根据productSN获取包装表中所有字段
        /// </summary>
        /// <param name="productSN"></param>
        /// <returns></returns>
        public PackageProductInfo GetAllPackagebyProductSN(string productSN)
        {
            PackageProductInfo test = null;
            SqlParameter parm = new SqlParameter(PARM_TEST_PRODUCTSN, SqlDbType.NVarChar, 50);
            //Bind the parameter
            parm.Value = productSN;

            //Execute the query	
            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PackageTest_SelectAllbyProductSN", parm))
            {
                if (rdr.Read())
                {
                    test = new PackageProductInfo(rdr.GetValue(0).ToString(), rdr.GetValue(1).ToString(), rdr.GetValue(2).ToString(), rdr.GetValue(3).ToString(), rdr.GetDateTime(4), rdr.GetValue(5).ToString(), rdr.GetValue(6).ToString(), rdr.GetDateTime(7));
                    return test;
                }
                return test;
            }
        }

        /// <summary>
        /// 按产品类型、时间段获取关联记录
        /// </summary>
        /// <param name="productTypeID"></param>
        /// <param name="starttime"></param>
        /// <param name="stoptime"></param>
        /// <returns></returns>
        public IList<PackageProductInfo> GetPackageTestsbyPIDDT(string productTypeID, DateTime starttime, DateTime stoptime)
        {
            IList<PackageProductInfo> tests = new List<PackageProductInfo>();
            SqlParameter[] testParms = GetPIDDTParameters();
            //Bind the parameter
            testParms[0].Value = productTypeID;
            testParms[1].Value = starttime;
            testParms[2].Value = stoptime;

            //Execute the query	
            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PackageTest_selectbyPIDDT", testParms))
            {
                while (rdr.Read())
                {
                    PackageProductInfo test = new PackageProductInfo(rdr.GetValue(0).ToString(), rdr.GetValue(1).ToString(), rdr.GetValue(2).ToString(), rdr.GetValue(3).ToString(), rdr.GetDateTime(4));
                    tests.Add(test);
                }
            }
            return tests;
        }

        /// <summary>
        /// 按关联员工、时间段获取关联记录
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="starttime"></param>
        /// <param name="stoptime"></param>
        /// <returns></returns>
        public IList<PackageProductInfo> GetPackageTestsbyUIDDT(string userID, DateTime starttime, DateTime stoptime)
        {
            IList<PackageProductInfo> tests = new List<PackageProductInfo>();
            SqlParameter[] testParms = GetUIDDTParameters();
            //Bind the parameter
            testParms[0].Value = userID;
            testParms[1].Value = starttime;
            testParms[2].Value = stoptime;

            //Execute the query	
            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PackageTest_selectbyUIDDT", testParms))
            {
                while (rdr.Read())
                {
                    PackageProductInfo test = new PackageProductInfo(rdr.GetValue(0).ToString(), rdr.GetValue(1).ToString(), rdr.GetValue(2).ToString(), rdr.GetValue(3).ToString(), rdr.GetDateTime(4));
                    tests.Add(test);
                }
            }
            return tests;
        }

        /// <summary>
        /// 按产品类型、时间段获取包装记录
        /// </summary>
        /// <param name="productTypeID"></param>
        /// <param name="starttime"></param>
        /// <param name="stoptime"></param>
        /// <returns></returns>
        public IList<PackageProductInfo> GetPackagedProductsbyPIDDT(string productTypeID, DateTime starttime, DateTime stoptime)
        {
            IList<PackageProductInfo> tests = new List<PackageProductInfo>();
            SqlParameter[] testParms = GetPIDDTParameters();
            //Bind the parameter
            testParms[0].Value = productTypeID;
            testParms[1].Value = starttime;
            testParms[2].Value = stoptime;

            //Execute the query	
            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PackageTest_selectPackagedbyPIDDT", testParms))
            {
                while (rdr.Read())
                {
                    PackageProductInfo test = new PackageProductInfo(rdr.GetValue(0).ToString(), rdr.GetValue(1).ToString(), rdr.GetValue(2).ToString(), rdr.GetValue(3).ToString(), rdr.GetDateTime(4), rdr.GetValue(5).ToString(), rdr.GetValue(6).ToString(), rdr.GetDateTime(7));
                    tests.Add(test);
                }
            }
            return tests;
        }

        /// <summary>
        /// 按包装员工、时间段获取包装记录
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="starttime"></param>
        /// <param name="stoptime"></param>
        /// <returns></returns>
        public IList<PackageProductInfo> GetPackagedProductsbyUIDDT(string userID, DateTime starttime, DateTime stoptime)
        {
            IList<PackageProductInfo> tests = new List<PackageProductInfo>();
            SqlParameter[] testParms = GetUIDDTParameters();
            //Bind the parameter
            testParms[0].Value = userID;
            testParms[1].Value = starttime;
            testParms[2].Value = stoptime;

            //Execute the query	
            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PackageTest_selectPackagedbyUIDDT", testParms))
            {
                while (rdr.Read())
                {
                    PackageProductInfo test = new PackageProductInfo(rdr.GetValue(0).ToString(), rdr.GetValue(1).ToString(), rdr.GetValue(2).ToString(), rdr.GetValue(3).ToString(), rdr.GetDateTime(4), rdr.GetValue(5).ToString(), rdr.GetValue(6).ToString(), rdr.GetDateTime(7));
                    tests.Add(test);
                }
            }
            return tests;
        }

        public IList<PackageProductInfo> GetPackagedProductsbyPIDDT_forPrinter(string productTypeID, int quantity)
        {
            IList<PackageProductInfo> tests = new List<PackageProductInfo>();
            SqlParameter[] testParms = GetPrinterParameters();
            //Bind the parameter
            testParms[0].Value = productTypeID;
            testParms[1].Value = quantity;

            //Execute the query	
            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PackageTest_PackageProductSNforPrinter", testParms))
            {
                while (rdr.Read())
                {
                    PackageProductInfo test = new PackageProductInfo(rdr.GetValue(0).ToString(), rdr.GetValue(1).ToString(), rdr.GetValue(2).ToString(), rdr.GetValue(3).ToString(), rdr.GetDateTime(4), rdr.GetValue(5).ToString(), rdr.GetValue(6).ToString(), rdr.GetDateTime(7));
                    tests.Add(test);
                }
            }
            return tests;
        }

        public IList<PackageProductInfo> GetPackageTestsbyDT(DateTime starttime, DateTime stoptime)
        {
            IList<PackageProductInfo> tests = new List<PackageProductInfo>();
            SqlParameter[] testParms = GetDTParameters();
            //Bind the parameter
            testParms[0].Value = starttime;
            testParms[1].Value = stoptime;

            //Execute the query	
            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PackageTest_selectbyDT", testParms))
            {
                while (rdr.Read())
                {
                    PackageProductInfo test = new PackageProductInfo(rdr.GetValue(0).ToString(), rdr.GetValue(1).ToString(), rdr.GetValue(2).ToString(), rdr.GetValue(3).ToString(), rdr.GetDateTime(4));
                    tests.Add(test);
                }
            }
            return tests;
        }

        public IList<PackageProductInfo> GetPackagedProductsbyDT(DateTime starttime, DateTime stoptime)
        {
            IList<PackageProductInfo> tests = new List<PackageProductInfo>();
            SqlParameter[] testParms = GetDTParameters();
            //Bind the parameter
            testParms[0].Value = starttime;
            testParms[1].Value = stoptime;

            //Execute the query	
            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PackageTest_selectPackagedbyDT", testParms))
            {
                while (rdr.Read())
                {
                    PackageProductInfo test = new PackageProductInfo(rdr.GetValue(0).ToString(), rdr.GetValue(1).ToString(), rdr.GetValue(2).ToString(), rdr.GetValue(3).ToString(), rdr.GetDateTime(4), rdr.GetValue(5).ToString(), rdr.GetValue(6).ToString(), rdr.GetDateTime(7));
                    tests.Add(test);
                }
            }
            return tests;
        }

        public int GetQbyCurrentDay()
        {
            int n = Convert.ToInt32(sqlHelper.ExecuteScalar(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PackageTest_CountbyCurrentDay", null));
            return n;
        }

        /// <summary>
        /// 按时间段获取各产品类型的包装数量
        /// </summary>
        /// <param name="starttime"></param>
        /// <param name="stoptime"></param>
        /// <returns></returns>
        public IList<Time_QInfo> GetPackagedQbyAllPIDDT(DateTime starttime, DateTime stoptime)
        {
            IList<Time_QInfo> tests = new List<Time_QInfo>();
            SqlParameter[] testParms = GetDTParameters();
            //Bind the parameter
            testParms[0].Value = starttime;
            testParms[1].Value = stoptime;

            //Execute the query	
            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PackageTest_countPackagedbyAllPIDDT", testParms))
            {
                while (rdr.Read())
                {
                    Time_QInfo test = new Time_QInfo(rdr.GetValue(0).ToString(), rdr.GetInt32(1));
                    tests.Add(test);
                }
            }
            return tests;
        }
        #endregion


        #region   流程监控相关
        /// <summary>
        /// 总是返回腔体序列号
        /// </summary>
        /// <param name="productSN"></param>
        /// <returns></returns>
        public string IsUnionProductSN(string productSN)
        {
            SqlParameter parm = new SqlParameter(PARM_TEST_PRODUCTSN, SqlDbType.NVarChar, 100);
            //Bind the parameter
            parm.Value = productSN;

            string str = Convert.ToString(sqlHelper.ExecuteScalar(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "IsUnionProductSN", parm));
            return str;
        }

        public string Isvalid3bk(string ProductTypeName, string SN3BK)
        {
            SqlParameter[] testParms = Get3BKParameters();
            //Bind the parameter
            testParms[0].Value = ProductTypeName;
            testParms[1].Value = SN3BK;

            string str = Convert.ToString(sqlHelper.ExecuteScalar(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "ProductType_Isvalid3bk", testParms));
            return str;
        }

        /// <summary>
        /// 关联时判断S代码是否正确
        /// </summary>
        /// <param name="productSN"></param>
        /// <param name="productTypeID"></param>
        /// <returns></returns>
        public bool IsvalidPID(string productSN, string productTypeID)
        {
            SqlParameter[] testParms = GetSNPIDParameters();
            testParms[0].Value = productSN;
            testParms[1].Value = productTypeID;

            bool str = Convert.ToBoolean(sqlHelper.ExecuteScalar(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PackageTest_IsvalidPID", testParms));
            return str;
        }

        public string IsvalidMatchingSN(string packageProductSN, string ProductTypeName)
        {
            SqlParameter[] testParms = GetPPSNPIDParameters();
            //Bind the parameter
            testParms[0].Value = packageProductSN;
            testParms[1].Value = ProductTypeName;

            string str = Convert.ToString(sqlHelper.ExecuteScalar(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PackageTest_IsvalidMatchingSN", testParms));
            return str;
        }

        public string IsvalidMatchingSN_SN3BK(string packageProductSN)
        {
            SqlParameter parm = new SqlParameter(PARM_TEST_PACKAGEPRODUCTSN, SqlDbType.NVarChar, 100);
            //Bind the parameter
            parm.Value = packageProductSN;

            string str = Convert.ToString(sqlHelper.ExecuteScalar(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PackageTest_IsvalidMatchingSN_SN3BK", parm));
            return str;
        }

        public string GetProductTypeID(string ProductSN)
        {
            SqlParameter parm = new SqlParameter(PARM_TEST_PRODUCTSN, SqlDbType.NVarChar, 50);
            //Bind the parameter
            parm.Value = ProductSN;

            string str = Convert.ToString(sqlHelper.ExecuteScalar(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "Test_selectProductTypeID", parm));
            return str;
        }
        #endregion


        #region   诺西产品外箱标签打印相关
        public int GetCountforPrint(string productTypeID)
        {
            SqlParameter parm = new SqlParameter(PARM_TEST_PRODUCTTYPEID, SqlDbType.NVarChar, 50);
            //Bind the parameter
            parm.Value = productTypeID;

            int str = Convert.ToInt32(sqlHelper.ExecuteScalar(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PackageTest_CountbynotPrint", parm));
            return str;
        }

        public string[] GetPackageProductSNsforPrint(string sN3BK, int amount)
        {
            List<string> list = new List<string>();
            SqlParameter[] testParms = GetPrintParameters();
            //Bind the parameter
            testParms[0].Value = sN3BK;
            testParms[1].Value = amount;

            using (SqlDataReader rdr = sqlHelper.ExecuteReader(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PackageTest_SelectforPrint", testParms))
            {
                while (rdr.Read())
                {
                    //打印条码字母大写
                    list.Add(rdr.GetValue(0).ToString().ToUpper());
                }
            }
            string[] productSNs = list.ToArray();
            return productSNs;
        }

        public bool SetPackageProductSNsPrinted(string sN3BK, int amount)
        {
            try
            {
                SqlParameter[] testParms = GetPrintParameters();
                //Bind the parameter
                testParms[0].Value = sN3BK;
                testParms[1].Value = amount;
                SqlCommand cmd = new SqlCommand();
                foreach (SqlParameter parm in testParms)
                    cmd.Parameters.Add(parm);
                using (SqlConnection conn = new SqlConnection(sqlHelper.ConnectionStringLocalTransaction))
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "PackageTest_PrintCheck";
                    using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { }
                }
                cmd.Parameters.Clear();
                return true;
            }
            catch
            {
                return false;
            }
        }
        #endregion


        #region 七大工序与有源工序管控相关
        public bool Package_HasValidSevenStep(string productSN, string productTypeID)
        {
            SqlParameter[] testParms = GetStepParameters();
            testParms[0].Value = productSN;
            testParms[1].Value = productTypeID;
            testParms[2].Value = "PackageStep";
            testParms[3].Direction = ParameterDirection.Output;

            sqlHelper.ExecuteNonQuery(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "ProductType_Flow_HasValidPostStep", testParms);
            bool isvalid = (bool)testParms[3].Value;
            return isvalid;
        }

        public bool Package_HasValidRFSourceStep(string productSN, string productTypeID)
        {
            SqlParameter[] testParms = GetStepParameters();
            testParms[0].Value = productSN;
            testParms[1].Value = productTypeID;
            testParms[2].Value = "PackageStep";
            testParms[3].Direction = ParameterDirection.Output;

            bool isvalid = Convert.ToBoolean(sqlHelper.ExecuteScalar(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "ProductType_Step_HasValidPostStep", testParms));
            return isvalid;
        }

        public bool IsValid_NineStep_DataCheck(string productSN, out string message)
        {
            SqlParameter[] testParms = GetSNRMParameters();
            testParms[0].Value = productSN;
            testParms[1].Direction = ParameterDirection.Output;

            bool isvalid = Convert.ToBoolean(sqlHelper.ExecuteScalar(sqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "PackageTest_KeyProcess_DataCheck", testParms));
            message = testParms[1].Value.ToString();
            return isvalid;
        }
        #endregion

        #region 返修相关
        public void AutoUnionforPackageProductSN(PackageProductInfo ppi)
        {
            SqlParameter[] testParms = GetAllParameters();
            //Bind the parameter
            testParms[0].Value = ppi.PackageProductSN;
            testParms[1].Value = ppi.ProductSN;
            testParms[2].Value = ppi.ProductTypeID;
            testParms[3].Value = ppi.UserID;
            testParms[4].Value = ppi.UnionTime;
            SqlCommand cmd = new SqlCommand();
            foreach (SqlParameter parm in testParms)
                cmd.Parameters.Add(parm);
            using (SqlConnection conn = new SqlConnection(sqlHelper.ConnectionStringLocalTransaction))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "PackageTest_AutoUnionforPackageProductSN";
                using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { }
            }
            cmd.Parameters.Clear();
        }

        public bool AutoBatchUnionforPackageProductSNs(PackageProductInfo ppi, DataTable itemdt)
        {
            List<SqlParameter> ItemList = new List<SqlParameter>() {
            sqlHelper.CreateSqlParemeterStructured(PARM_PACKAGEPRODUCT_DT,itemdt)};
            SqlParameter[] testParms = GetAllParameters();
            //Bind the parameter
            testParms[0].Value = ppi.PackageProductSN;
            testParms[1].Value = ppi.ProductSN;
            testParms[2].Value = ppi.ProductTypeID;
            testParms[3].Value = ppi.UserID;
            testParms[4].Value = ppi.UnionTime;

            SqlCommand cmd = new SqlCommand();
            foreach (SqlParameter parm in testParms)
                cmd.Parameters.Add(parm);
            foreach (SqlParameter parm in ItemList)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(sqlHelper.ConnectionStringLocalTransaction))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    cmd.CommandText = "PackageTest_AutoBatchUnionforPackageProductSNs";
                    using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { }
                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }

        #endregion
    }
}
